package com.imut.lagain.repository;
import org.apache.ibatis.annotations.Select;
import org.slf4j.LoggerFactory;

import com.imut.lagain.entity.UnlockHistory;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;


import java.time.LocalDateTime;
import java.util.List;

/**
 * 解锁历史数据访问层
 */
@Mapper
public interface UnlockHistoryRepository extends BaseMapper<UnlockHistory> {

    /**
     * 根据锁ID查询解锁历史
     */
    List<UnlockHistory> findByLockIdOrderByCreatedAtDesc(Long lockId);

    /**
     * 根据用户ID查询解锁历史
     */
    List<UnlockHistory> findByUserIdOrderByCreatedAtDesc(Long userId);

    /**
     * 根据锁ID和用户ID查询解锁历史
     */
    List<UnlockHistory> findByLockIdAndUserIdOrderByCreatedAtDesc(Long lockId, Long userId);

    /**
     * 根据锁ID和尝试类型查询解锁历史
     */
    List<UnlockHistory> findByLockIdAndAttemptTypeOrderByCreatedAtDesc(Long lockId, String attemptType);

    /**
     * 根据锁ID和尝试结果查询解锁历史
     */
    List<UnlockHistory> findByLockIdAndAttemptResultOrderByCreatedAtDesc(Long lockId, String attemptResult);

    /**
     * 根据用户ID和时间范围查询解锁历史
     */
    @Select("SELECT u FROM UnlockHistory u WHERE u.userId = :userId AND u.createdAt BETWEEN :startDate AND :endDate ORDER BY u.createdAt DESC")
    List<UnlockHistory> findByUserIdAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 根据锁ID和时间范围查询解锁历史
     */
    @Select("SELECT u FROM UnlockHistory u WHERE u.lockId = :lockId AND u.createdAt BETWEEN :startDate AND :endDate ORDER BY u.createdAt DESC")
    List<UnlockHistory> findByLockIdAndDateRange(@Param("lockId") Long lockId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 统计锁的总尝试次数
     */
    @Select("SELECT COUNT(u) FROM UnlockHistory u WHERE u.lockId = :lockId")
    Long countByLockId(@Param("lockId") Long lockId);

    /**
     * 统计锁的成功解锁次数
     */
    @Select("SELECT COUNT(u) FROM UnlockHistory u WHERE u.lockId = :lockId AND u.attemptResult = 'success'")
    Long countSuccessfulAttemptsByLockId(@Param("lockId") Long lockId);

    /**
     * 统计锁的失败尝试次数
     */
    @Select("SELECT COUNT(u) FROM UnlockHistory u WHERE u.lockId = :lockId AND u.attemptResult = 'failed'")
    Long countFailedAttemptsByLockId(@Param("lockId") Long lockId);

    /**
     * 统计用户的总尝试次数
     */
    @Select("SELECT COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户的成功解锁次数
     */
    @Select("SELECT COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId AND u.attemptResult = 'success'")
    Long countSuccessfulAttemptsByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各尝试类型的次数
     */
    @Select("SELECT u.attemptType, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId GROUP BY u.attemptType")
    List<Object[]> countByUserIdGroupByAttemptType(@Param("userId") Long userId);

    /**
     * 统计用户各尝试结果的次数
     */
    @Select("SELECT u.attemptResult, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId GROUP BY u.attemptResult")
    List<Object[]> countByUserIdGroupByAttemptResult(@Param("userId") Long userId);

    /**
     * 查询用户最近的解锁历史
     */
    List<UnlockHistory> findTop10ByUserIdOrderByCreatedAtDesc(Long userId);

    /**
     * 查询锁最近的解锁历史
     */
    List<UnlockHistory> findTop10ByLockIdOrderByCreatedAtDesc(Long lockId);

    /**
     * 查询用户平均解锁耗时
     */
    @Select("SELECT AVG(u.duration) FROM UnlockHistory u WHERE u.userId = :userId AND u.attemptResult = 'success'")
    Double getAverageUnlockDurationByUserId(@Param("userId") Long userId);

    /**
     * 查询锁平均解锁耗时
     */
    @Select("SELECT AVG(u.duration) FROM UnlockHistory u WHERE u.lockId = :lockId AND u.attemptResult = 'success'")
    Double getAverageUnlockDurationByLockId(@Param("lockId") Long lockId);

    /**
     * 查询用户解锁成功率
     */
    @Select("SELECT (COUNT(CASE WHEN u.attemptResult = 'success' THEN 1 END) * 100.0 / COUNT(*)) FROM UnlockHistory u WHERE u.userId = :userId")
    Double getUnlockSuccessRateByUserId(@Param("userId") Long userId);

    /**
     * 查询锁解锁成功率
     */
    @Select("SELECT (COUNT(CASE WHEN u.attemptResult = 'success' THEN 1 END) * 100.0 / COUNT(*)) FROM UnlockHistory u WHERE u.lockId = :lockId")
    Double getUnlockSuccessRateByLockId(@Param("lockId") Long lockId);

    /**
     * 查询用户最常用的解锁方式
     */
    @Select("SELECT u.attemptType FROM UnlockHistory u WHERE u.userId = :userId AND u.attemptResult = 'success' GROUP BY u.attemptType ORDER BY COUNT(u) DESC")
    List<String> getMostUsedUnlockMethodsByUserId(@Param("userId") Long userId);

    /**
     * 查询用户最常失败的原因
     */
    @Select("SELECT u.failureReason FROM UnlockHistory u WHERE u.userId = :userId AND u.attemptResult = 'failed' AND u.failureReason IS NOT NULL GROUP BY u.failureReason ORDER BY COUNT(u) DESC")
    List<String> getMostCommonFailureReasonsByUserId(@Param("userId") Long userId);

    /**
     * 查询可疑的解锁尝试（基于IP地址）
     */
    @Select("SELECT u FROM UnlockHistory u WHERE u.lockId = :lockId AND u.ipAddress = :ipAddress AND u.attemptResult = 'failed' AND u.createdAt >= :threshold ORDER BY u.createdAt DESC")
    List<UnlockHistory> findSuspiciousAttemptsByIp(@Param("lockId") Long lockId, @Param("ipAddress") String ipAddress, @Param("threshold") LocalDateTime threshold);

    /**
     * 查询高风险解锁尝试（多次失败）
     */
    @Select("SELECT u.lockId, u.ipAddress, COUNT(u) as failureCount FROM UnlockHistory u WHERE u.attemptResult = 'failed' AND u.createdAt >= :threshold GROUP BY u.lockId, u.ipAddress HAVING COUNT(u) >= :minFailures ORDER BY failureCount DESC")
    List<Object[]> findHighRiskAttempts(@Param("threshold") LocalDateTime threshold, @Param("minFailures") Long minFailures);

    /**
     * 查询用户在特定时间段的活动
     */
    @Select("SELECT HOUR(u.createdAt) as hour, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId GROUP BY HOUR(u.createdAt) ORDER BY hour")
    List<Object[]> getUserActivityByHour(@Param("userId") Long userId);

    /**
     * 查询用户使用的设备信息
     */
    @Select("SELECT u.deviceInfo, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId AND u.deviceInfo IS NOT NULL GROUP BY u.deviceInfo ORDER BY COUNT(u) DESC")
    List<Object[]> getUserDeviceUsage(@Param("userId") Long userId);

    /**
     * 查询用户的地理位置分布
     */
    @Select("SELECT u.locationInfo, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId AND u.locationInfo IS NOT NULL GROUP BY u.locationInfo ORDER BY COUNT(u) DESC")
    List<Object[]> getUserLocationDistribution(@Param("userId") Long userId);

    /**
     * 查询用户的情感状态分布
     */
    @Select("SELECT u.emotionalState, COUNT(u) FROM UnlockHistory u WHERE u.userId = :userId AND u.emotionalState IS NOT NULL GROUP BY u.emotionalState ORDER BY COUNT(u) DESC")
    List<Object[]> getUserEmotionalStateDistribution(@Param("userId") Long userId);

    /**
     * 查询系统解锁统计
     */
    @Select("SELECT COUNT(u), COUNT(CASE WHEN u.attemptResult = 'success' THEN 1 END), COUNT(CASE WHEN u.attemptResult = 'failed' THEN 1 END) FROM UnlockHistory u")
    Object[] getSystemUnlockStatistics();

    /**
     * 查询最活跃的用户
     */
    @Select("SELECT u.userId, COUNT(u) as attemptCount FROM UnlockHistory u WHERE u.createdAt >= :threshold GROUP BY u.userId ORDER BY attemptCount DESC")
    List<Object[]> getMostActiveUsers(@Param("threshold") LocalDateTime threshold);

    /**
     * 查询最受欢迎的锁
     */
    @Select("SELECT u.lockId, COUNT(u) as attemptCount FROM UnlockHistory u WHERE u.createdAt >= :threshold GROUP BY u.lockId ORDER BY attemptCount DESC")
    List<Object[]> getMostPopularLocks(@Param("threshold") LocalDateTime threshold);

    /**
     * 删除过期的解锁历史
     */
    @Select("DELETE FROM UnlockHistory u WHERE u.createdAt < :threshold")
    int deleteExpiredHistory(@Param("threshold") LocalDateTime threshold);
}
